package org.hales.util;

import cn.hutool.core.date.LocalDateTimeUtil;
import cn.hutool.poi.excel.ExcelWriter;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.streaming.SXSSFSheet;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.hales.annotation.ExcelReadFieldName;
import org.hales.annotation.IndexAlias;
import org.hales.vo.ExcelFieldVO;
import org.springframework.beans.BeanWrapperImpl;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.lang.reflect.Field;
import java.math.BigDecimal;
import java.net.URLEncoder;
import java.nio.file.Files;
import java.time.LocalDate;
import java.util.*;
import java.util.stream.Collectors;

/**
 * @author 陈建康
 */
public class ExcelUtil {

    /**
     * 读取Excel文件
     * @param file 文件
*      @param sheetIndex  工作表序号, 从0开始
     * @param headIndex  标题行所在行号, 从0开始
     * @return {@link List }<{@link Map }<{@link String }, {@link Object }>>
     */
    public static List<Map<String, Object>> readExcelFile(Object file,int sheetIndex,int headIndex) {
        List<Map<String, Object>> collectedData = new ArrayList<>();
        try (InputStream inputStream = convertToInputStream(file);
             Workbook workbook = new XSSFWorkbook(inputStream)) {

            // 根据 sheetIndex 获取工作表
            Sheet sheet = workbook.getSheetAt(sheetIndex);
            // 根据 headIndex 获取标题行
            Row headerRow = sheet.getRow(headIndex);
            List<String> headers = new ArrayList<>();
            for (Cell cell : headerRow) {
                headers.add(cell.getStringCellValue());
            }

            // 遍历其余行并收集数据
            for (int i = headIndex + 1; i <= sheet.getLastRowNum(); i++) {
                Row row = sheet.getRow(i);
                if (row != null) {
                    Map<String, Object> rowData = new HashMap<>();
                    for (int j = 0; j < headers.size(); j++) {
                        Cell cell = row.getCell(j);
                        if (cell != null) {
                            switch (cell.getCellType()) {
                                case STRING:
                                    rowData.put(headers.get(j), cell.getStringCellValue());
                                    break;
                                case NUMERIC:
                                    rowData.put(headers.get(j), new BigDecimal(Double.toString(cell.getNumericCellValue())).toPlainString());
                                    break;
                                case BOOLEAN:
                                    rowData.put(headers.get(j), cell.getBooleanCellValue());
                                    break;
                                case FORMULA:
                                    // 如果公式结果是数值或字符串，也强制转为字符串
                                    rowData.put(headers.get(j), formatFormulaCellValue(cell));
                                    break;
                                case BLANK:
                                    rowData.put(headers.get(j), "");
                                    break;
                                default:
                                    rowData.put(headers.get(j), null);
                            }
                        }
                    }
                    collectedData.add(rowData);
                }
            }
        } catch (IOException e) {
            throw new RuntimeException(e);
        }
        return collectedData;
    }

    // 提取公共方法：将不同输入转换为InputStream
    private static InputStream convertToInputStream(Object fileInput) throws IOException {
        if (fileInput instanceof MultipartFile) {
            return ((MultipartFile) fileInput).getInputStream();
        } else if (fileInput instanceof File) {
            return Files.newInputStream(((File) fileInput).toPath());
        } else if (fileInput instanceof InputStream) {
            return (InputStream) fileInput;
        } else {
            throw new IllegalArgumentException("Unsupported file input type: " + fileInput.getClass().getName());
        }
    }
    /**
     * 简单导出excel文件
     * @param response 响应
     * @param dataList 列表数据
     * @param clazz 集合数据的类型
     * @throws Exception
     */
    public static  <T> void exportExcelSingleSheet(HttpServletResponse response, List<T> dataList, Class<T> clazz) throws Exception {
        OutputStream out ;
        // 获取excel导出字段标题
        List<ExcelFieldVO> headList = ExcelFieldVO.parseFields(clazz);
        String fileName = headList.get(0).getFieldName() + LocalDateTimeUtil.format(LocalDate.now(), "yyyyMMddHHmmss") + ".xlsx";
        try (SXSSFWorkbook workbook = new SXSSFWorkbook()){
            response.setContentType("application/octet-stream");
            response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(fileName, "UTF-8"));

            SXSSFSheet sheet = workbook.createSheet();
            workbook.setSheetName(0, "sheet1");

            // 表头
            Row head = sheet.createRow(0);
            for (int i = 0; i < headList.size(); i++) {
                head.createCell(i).setCellValue(headList.get(i).getHeadName());
            }

            // 填充数据
            for (int i= 0; i < dataList.size(); i++ ) {
                Row row = sheet.createRow(i + 1);
                for (int i1 = 0; i1 < headList.size(); i1++) {
                    String fieldName = headList.get(i1).getFieldName();
                    Field field = clazz.getDeclaredField(fieldName);
                    field.setAccessible(true);
                    row.createCell(i1).setCellValue((String) field.get(dataList.get(i)));
                }
            }
            out = response.getOutputStream();
            workbook.write(out);
            out.flush();
            out.close();
        }catch (Exception e){
            throw e;
        }
    }

    private static String formatFormulaCellValue(Cell cell) {
        try {
            // 尝试获取公式的计算结果
            switch (cell.getCachedFormulaResultType()) {
                case STRING:
                    return cell.getStringCellValue();
                case NUMERIC:
                    return formatNumericCellValue(cell);
                default:
                    return cell.toString();
            }
        } catch (Exception e) {
            // 如果公式无法解析，返回空字符串或其他默认值
            return "";
        }
    }

    private static String formatNumericCellValue(Cell cell) {
        if (DateUtil.isCellDateFormatted(cell)) {
            // 如果是日期格式，可以按需格式化日期
            return cell.getDateCellValue().toString();
        } else {
            double value = cell.getNumericCellValue();
            BigDecimal df = new BigDecimal((Double.toString(value)));
            return df.toPlainString();
        }
    }

    /**
     * <p>将 Map 对象转换为指定类型的实例</p>
     * <P>此方法主要用于从 Map 中提取数据并填充到一个新创建的 POJO 对象中</P>
     * <p>它通过字段名与 Map 中键的映射来实现数据的转换</p>
     *
     * @param map 存储数据的 Map，键为字段的中文名，值为字段的值
     * @param clazz 目标 POJO 类的 Class 对象
     * @param <T> 泛型参数，表示目标 POJO 类的类型
     * @return 转换后的 POJO 类的实例
     * @throws RuntimeException 如果转换过程中发生错误，抛出运行时异常
     */
    public static <T> T convert(Map<String, Object> map, Class<T> clazz) {
        try {
            // 获取无参构造方法，创建实例对象
            T instance = clazz.getDeclaredConstructor().newInstance();
            // BeanWrapperImpl 是其实现类，封装了对目标对象的属性访问和类型转换逻辑
            BeanWrapperImpl wrapper = new BeanWrapperImpl(instance);
            // 设置自动扩展嵌套路径，以支持复杂对象的属性设置
            wrapper.setAutoGrowNestedPaths(true);
            // 获取字段与中文名的映射关系
            Map<String, String> fieldMappings = getFieldMappings(clazz);

            // 遍历 Map 中的每个键值对
            for (Map.Entry<String, Object> entry : map.entrySet()) {
                // 获取中文键名
                String chineseKey = entry.getKey();
                // 根据中文键名获取对应的字段名
                String fieldName = fieldMappings.get(chineseKey);
                if (fieldName != null) {
                    // 获取值
                    Object value = entry.getValue();
                    // 直接使用 BeanWrapper 的类型转换能力
                    wrapper.setPropertyValue(fieldName, value);
                }
            }
            // 返回填充后的实例
            return instance;
        } catch (Exception e) {
            // 如果发生异常，抛出运行时异常
            throw new RuntimeException("Map转换POJO失败", e);
        }
    }

    /**
     * 获取类的中文字段映射
     * 此方法用于获取目标类中带有 ExcelReadFieldName 注解的字段映射
     * 它将字段的中文名映射到字段的实际名
     *
     * @param clazz 目标类的 Class 对象
     * @return 字段中文名到字段实际名的映射
     */
    private static Map<String, String> getFieldMappings(Class<?> clazz) {
        return Arrays.stream(clazz.getDeclaredFields())
                // 过滤出带有 ExcelReadFieldName 注解的字段
                .filter(f -> f.isAnnotationPresent(ExcelReadFieldName.class))
                .collect(Collectors.toMap(
                        // 将字段的中文名作为键
                        f -> f.getAnnotation(ExcelReadFieldName.class).value(),
                        // 将字段的实际名作为值
                        Field::getName
                ));
    }

    public static <T> void  writeToServlet(HttpServletResponse response, List<T> rows, Class<T> clazz) throws IOException {
        try (
                ExcelWriter writer = cn.hutool.poi.excel.ExcelUtil.getWriter(true);
                ServletOutputStream out = response.getOutputStream()
        ) {
            // 设置响应头
            response.setContentType("application/octet-stream");
            response.setHeader("Content-Disposition", "attachment;filename=" + getAliasFileName(clazz) +".xlsx");

            // 写入数据并刷新到输出流
            setAliasOrder(writer,clazz);
            writer.setOnlyAlias(true);
            writer.write(rows, true);
            writer.flush(out, true);
        } catch (IOException e) {
            // 异常处理，记录日志或返回错误信息
            e.printStackTrace();
            throw new RuntimeException("Excel 导出失败", e);
        }
    }

    private static String getAliasFileName(Class<?> clazz) throws UnsupportedEncodingException {
        String fileName = "Excel文件导出";
        if (clazz.isAnnotationPresent(IndexAlias.class)){
            fileName =  clazz.getAnnotation(IndexAlias.class).fileName();
        }
        return URLEncoder.encode(fileName,"UTF-8").replaceAll("\\+", "%20");
    }
    public static void setAliasOrder(ExcelWriter excelWriter , Class<?> clazz){
        Arrays.stream(clazz.getDeclaredFields()).filter(field -> field.isAnnotationPresent(IndexAlias.class))
                .sorted((field1, field2) -> {
                    IndexAlias annotation1 = field1.getAnnotation(IndexAlias.class);
                    IndexAlias annotation2 = field2.getAnnotation(IndexAlias.class);
                    return Integer.compare(annotation1.index(), annotation2.index());
                })
                .forEach(field -> {
                    IndexAlias annotation = field.getAnnotation(IndexAlias.class);
                    excelWriter.addHeaderAlias(field.getName(),annotation.alias());
                });
    }

}
